*****************************************************************************************

* This file imports and cleans the responses to the ICMA surveys
* The survey responses are available for purchase from ICMA, as described in the readme.
* Because the data is proprietary, it is not included in this replication package

*****************************************************************************************

/* steps in below file

1. clean each ICMA wave individually, extracting information on at-large and ward voting
2. append the waves together
3. harmonize place names and ids across years

*/


** set directory

* start by cleaning each ICMA survey individually (because each layout is slightly different)

//// 2011 ICMA survey
use "icma_2011.dta", clear

keep year - placefips_final q1 q12b_1d q12b_2d q26 - q27a_b ujuris u00pop icma_region_code
drop id

foreach v of varlist q1-q27a_b {
	local lab =  "`: var label `v''"
	local lab = "`lab' from ICMA - 2011"
	di "`lab'"
	label var `v' "`lab'"
}

// Rename important question variables
rename (q27 q12b_1d q12b_2d q27a_a q27a_b u00pop) (gov_type AL_to_W W_to_AL members_AL members_W pop2000)

// Create variables on number of at large and ward members
replace members_AL = q26 if gov_type == 1
replace members_W = q26 if gov_type == 2
replace members_AL = 0 if members_AL == . & members_W != .
replace members_W = 0 if members_W == . & members_AL != .
drop q26

// Tostring gov_type and name
tostring gov_type, replace
replace gov_type = "At-large" if gov_type == "1"
replace gov_type = "Ward" if gov_type == "2"
replace gov_type = "Mixed" if gov_type == "3"

// Save and move onto next year
order year statefips icma_region_code countyfips placefips_final ///
	imisid AL_to_W W_to_AL gov_type members_AL members_W
save "icma_2011_cleaned.dta", replace



//// 2006 ICMA
use "icma_2006.dta", replace
keep year - placefips_final q1 iMISID q7b_1d q7b_2d q21 q22* UJURIS U00POP ICMA_REGION_CODE

foreach v of varlist q1-q22b {
	local lab =  "`: var label `v''"
	local lab = "`lab' from ICMA - 2006"
	di "`lab'"
	label var `v' "`lab'"
}

// Rename question variables
rename (q22 q7b_1d q7b_2d q22a q22b U00POP iMISID UJURIS ICMA_REGION_CODE) ///
	(gov_type AL_to_W W_to_AL members_AL members_W pop2000 imisid ujuris icma_region_code)
	
// Create members variables
replace members_AL = q21 if gov_type == 1
replace members_W = q21 if gov_type == 2
replace members_AL = 0 if members_AL == . & members_W != .
replace members_W = 0 if members_W == . & members_AL != .
drop q21

// Tostring gov_type and name
tostring gov_type, replace
replace gov_type = "At-large" if gov_type == "1"
replace gov_type = "Ward" if gov_type == "2"
replace gov_type = "Mixed" if gov_type == "3"

// Save ICMA data and move onto next year
order year statefips icma_region_code countyfips placefips_final ///
	imisid AL_to_W W_to_AL gov_type members_AL members_W
save "icma_2006_cleaned.dta", replace



//// 2001 ICMA
use "icma_2001.dta", replace
keep year - placefips_final q1 iMISID q7b_1d q7b_2d q26a q26b UJURIS U00POP ICMA_REG

foreach v of varlist q1-q26b {
	local lab =  "`: var label `v''"
	local lab = "`lab' from ICMA - 2001"
	di "`lab'"
	label var `v' "`lab'"
}


// Rename question variables
rename (iMISID q7b_1d q7b_2d q26a q26b UJURIS U00POP ICMA_REG) (imisid AL_to_W to_mixed members_AL members_W ujuris pop2000 icma_region_code)

// Generate gov_type from member counts
replace members_AL = 0 if members_AL == . & members_W != .
replace members_W = 0 if members_W == . & members_AL != .

gen gov_type = "", after(to_mixed)
replace gov_type = "At-large" if members_AL > 0 & members_AL < . & members_W == 0
replace gov_type = "Ward" if members_W > 0 & members_W < . & members_AL == 0
replace gov_type = "Mixed" if members_W > 0 & members_W < . & members_AL > 0 & members_AL < .

// Save ICMA data and move onto next year
order year statefips icma_region_code countyfips placefips_final imisid AL_to_W to_mixed gov_type members_AL members_W
save "icma_2001_cleaned.dta", replace

//// 1996 ICMA
use "icma_1996.dta", replace

keep year - placefips_final q1 imisid q6b_1d q6b_2d q24a q24b ujuris u90pop icma_reg_code

foreach v of varlist q1-q24b {
	local lab =  "`: var label `v''"
	local lab = "`lab' from ICMA - 1996"
	di "`lab'"
	label var `v' "`lab'"
}

// Rename question variables
rename (q6b_1d q6b_2d q24a q24b icma_reg_code u90pop) ///
	(AL_to_W to_mixed members_AL members_W icma_region_code pop1990)

// Generate gov_type from member counts
replace members_AL = 0 if members_AL == . & members_W != .
replace members_W = 0 if members_W == . & members_AL != .

gen gov_type = ""
replace gov_type = "At-large" if members_AL > 0 & members_AL < . & members_W == 0
replace gov_type = "Ward" if members_W > 0 & members_W < . & members_AL == 0
replace gov_type = "Mixed" if members_W > 0 & members_W < . & members_AL > 0 & members_AL < .

// Save ICMA data and move onto next year
order AL_to_W to_mixed gov_type, after(imisid)
order icma_region_code, after(statefips)
save "icma_1996_cleaned.dta", replace




//// 1991 ICMA
use "icma_1991.dta", replace

keep year - placefips_final q1 q8b1-q8b5 matchid q87_1 - q91_3 q29_1 - q29_3 ujuris u85pop uregn

foreach v of varlist q1-q29_3 {
	local lab =  "`: var label `v''"
	local lab = "`lab' from ICMA - 1991"
	di "`lab'"
	label var `v' "`lab'"
}

// Rename question variables
rename (matchid q29_1 q29_2 q29_3 uregn u85pop) ///
	(imisid members_AL members_mixed members_W icma_region_code pop1985)

// Generate gov_type from member counts
replace members_AL = 0 if members_AL == . & (members_W != . | members_mixed != .)
replace members_W = 0 if members_W == . & (members_AL != . | members_mixed != .)
replace members_mixed = 0 if members_mixed == . & (members_W != . & members_AL != .)

gen gov_type = ""
replace gov_type = "At-large" if members_AL > 0 & members_AL < . & members_W == 0
replace gov_type = "Ward" if members_W > 0 & members_W < . & members_AL == 0
replace gov_type = "Mixed" if (members_W > 0 & members_W < . & members_AL > 0 ///
	& members_AL < .) | (members_mixed > 0 & members_mixed != .)
	
// Generate year that government switched
gen AL_to_W = .
replace AL_to_W = 1987 if q87_1 == 1 | q87_2 == 1 | q87_3 == 1 
replace AL_to_W = 1988 if q88_1 == 1 | q88_2 == 1 | q88_3 == 1 
replace AL_to_W = 1989 if q89_1 == 1 | q89_2 == 1 | q89_3 == 1 
replace AL_to_W = 1990 if q90_1 == 1 | q90_2 == 1 | q90_3 == 1 
replace AL_to_W = 1991 if q91_1 == 1 | q91_2 == 1 | q91_3 == 1

gen to_mixed = .
replace to_mixed = 1987 if q87_1 == 2 | q87_2 == 2 | q87_3 == 2 
replace to_mixed = 1988 if q88_1 == 2 | q88_2 == 2 | q88_3 == 2 
replace to_mixed = 1989 if q89_1 == 2 | q89_2 == 2 | q89_3 == 2 
replace to_mixed = 1990 if q90_1 == 2 | q90_2 == 2 | q90_3 == 2 
replace to_mixed = 1991 if q91_1 == 2 | q91_2 == 2 | q91_3 == 2 
drop q87_1 - q91_3

*For some subsample there is an answer to the question "Reason why you switched?" here we create a variable to track this*

gen why = ""
replace why = "Court" if q8b1 == 1 & (AL_to_W != . | to_mixed != .)
replace why = "Initiative" if q8b2 == 1 & (AL_to_W != . | to_mixed != .)
replace why = "State mandate" if q8b3 == 1 & (AL_to_W != . | to_mixed != .)
replace why = "Referendum" if q8b4 == 1 & (AL_to_W != . | to_mixed != .)
replace why = "Other" if q8b5 == 1 & (AL_to_W != . | to_mixed != .)

label var why "Self-reported reason why you switched government type"

// Region codes
tostring icma_region_code, replace
order icma_region_code, after(statefips)

// Save ICMA data and move onto next year
order AL_to_W to_mixed gov_type, after(imisid)
tempfile icma_1991
save "icma_1991_cleaned.dta", replace


//// 1986 ICMA
use "icma_1986.dta", replace
keep year - placefips_final q1 matchid q5pt2a q5pt2b q19* ujuris icma_reg_code u80pop u84pop

foreach v of varlist q1-q19d {
	local lab =  "`: var label `v''"
	local lab = "`lab' from ICMA - 1986"
	di "`lab'"
	label var `v' "`lab'"
}

// Rename question variables
rename (matchid q5pt2a q5pt2b q19a q19b q19c q19d icma_reg_code u80pop u84pop) (imisid AL_to_W to_mixed members_AL members_mixed members_W members_other icma_region_code pop1980 pop1984)

// Set the switches to 1984.. the midpoint of the time period
replace AL_to_W = 1984 if AL_to_W == 1	
replace to_mixed = 1984 if to_mixed == 1	
	
// Generate gov_type from member counts
replace members_mixed = members_other
drop members_other
replace members_AL = 0 if members_AL == . & (members_W != . | members_mixed != .)
replace members_W = 0 if members_W == . & (members_AL != . | members_mixed != .)
replace members_mixed = 0 if members_mixed == . & (members_W != . & members_AL != .)

gen gov_type = ""
replace gov_type = "At-large" if members_AL > 0 & members_AL < . & members_W == 0
replace gov_type = "Ward" if members_W > 0 & members_W < . & members_AL == 0
replace gov_type = "Mixed" if (members_W > 0 & members_W < . & members_AL > 0 ///
	& members_AL < .) | (members_mixed > 0 & members_mixed != .)

// Region codes
order icma_region_code, after(statefips)

// Save ICMA data and move onto next year
order AL_to_W to_mixed gov_type, after(imisid)
tempfile icma_1986
save "icma_1986_cleaned.dta", replace

//// 1981 ICMA
use "icma_1981.dta", replace
keep year - placefips_final q1 matchid q16 q17b_yes q17c_yes q19* ujuris icma_reg_code
drop q19_c1


foreach v of varlist q1-q19_d {
	local lab =  "`: var label `v''"
	local lab = "`lab' from ICMA - 1981"
	di "`lab'"
	label var `v' "`lab'"
}
// Rename question variables
rename (matchid q19_a q19_b q19_c q19_d icma_reg_code) ///
	(imisid members_AL members_mixed members_W members_other icma_region_code)

// Generate gov_type from member counts
replace members_mixed = members_other
drop members_other
replace members_AL = 0 if members_AL == . & (members_W != . | members_mixed != .)
replace members_W = 0 if members_W == . & (members_AL != . | members_mixed != .)
replace members_mixed = 0 if members_mixed == . & (members_W != . & members_AL != .)

gen gov_type = ""
replace gov_type = "At-large" if members_AL > 0 & members_AL < . & members_W == 0
replace gov_type = "Ward" if members_W > 0 & members_W < . & members_AL == 0
replace gov_type = "Mixed" if (members_W > 0 & members_W < . & members_AL > 0 ///
	& members_AL < .) | (members_mixed > 0 & members_mixed != .)
	
// Create switch variables
replace q17b_yes = 1900 + q17b_yes 
gen AL_to_W = q17b_yes if q17c_yes == 1 & q16 == 2
gen W_to_AL = q17b_yes if q17c_yes == 2 & q16 == 1
gen to_mixed = q17b_yes if q16 == 3 & q17c_yes != .
drop q16 - q17c_yes

// Region codes
order icma_region_code, after(statefips)

// Save ICMA data and move onto next year
order AL_to_W W_to_AL to_mixed gov_type, after(imisid)
save "icma_1981_cleaned.dta", replace


//// append the results from each year

use "icma_2011_cleaned.dta"
append using "icma_2006_cleaned.dta"
append using "icma_2001_cleaned.dta"
append using "icma_1996_cleaned.dta"
append using "icma_1991_cleaned.dta"
append using "icma_1986_cleaned.dta"
append using "icma_1981_cleaned.dta"

order to_mixed, after(W_to_AL)

// fill in missing census region variables using places in the same county
bysort statefips countyfips (placefips_final year): egen region = mode(icma_region_code)
replace icma_region_code = region
drop region
bysort statefips (countyfips placefips_final year): replace icma_region_code = icma_region_code[_n-1] ///
	if icma_region_code[_n-1] != "" & inlist(icma_region_code, "", "1", "2", "3", "4", "44")
	
// remove some duplicates by hand (these duplicates appear in the raw ICMA data)
drop if year == 2011 & statefips == 5 & placefips_final == . & countyfips == 49960
drop if year == 1996 & statefips == 9 & placefips_final == 62710 & imisid == 173109
drop if year == 1996 & statefips == 12 & placefips_final == 62500 & imisid == 124256
drop if year == 2001 & statefips == 12 & placefips_final == 62500 & imisid == 182452
drop if year == 1991 & statefips == 26 & placefips_final == 51900 & imisid == 273850
drop if year == 1996 & statefips == 26 & placefips_final == 51900 & imisid == 189850
drop if year == 1991 & statefips == 36 & placefips_final == 79174 & imisid == 311640 
drop if year == 2001 & statefips == 36 & placefips_final == 44831 & imisid == 175078 
drop if year == . & countyfips == . & placefips_final == .
isid year statefip countyfips placefips_final 

//// standardize place name
order ujuris, after(placefips_final)
replace ujuris = strproper(ujuris)
gen place = ujuris, after(ujuris)

gen place_type = "", after(place)
replace place_type = "City" if strpos(place, "City Of ") > 0
replace place_type = "Town" if strpos(place, "Town Of ") > 0
replace place_type = "Township" if strpos(place, "Township Of ") > 0
replace place_type = "Village" if strpos(place, "Village Of ") > 0
replace place_type = "City and Borough" if strpos(place, "City And Borough Of ") > 0
replace place_type = "Borough" if strpos(place, "Borough Of ") > 0

replace place = subinstr(place, "City Of ", "", .)
replace place = subinstr(place, "Town Of ", "", .)
replace place = subinstr(place, "Township Of ", "", .)
replace place = subinstr(place, "Village Of ", "", .)
replace place = subinstr(place, "City And Borough Of ", "", .)
replace place = subinstr(place, "Borough Of ", "", .)

// Fill in place_type using surrounding obs
bysort statefips countyfips placefips_final (year): ///
	replace place_type = place_type[_n-1] if place_type == "" & place_type[_n-1] != ""
bysort statefips countyfips placefips_final (year): ///
	replace place_type = place_type[_n+1] if place_type == "" & place_type[_n+1] != ""
	
	
rename (place place_type) (place_icma place_type_icma)	

* Create a unique place fips id by combining the various fips codes that are included in the ICMA data
gen str_pl_fips = string(placefips) if placefips >= 10000
replace str_pl_fips = "0" + string(placefips) if placefips < 10000 & placefips > 999
replace str_pl_fips = "00" + string(placefips) if placefips < 999
gen str_cty_fips = string(countyfips) if countyfips > 99
replace str_cty_fips = "0" + string(countyfips) if countyfips < 100 & countyfips > 9
replace str_cty_fips = "00" + string(countyfips) if countyfips < 10
replace str_pl_fips = "00" + string(placefips) if placefips < 999 & placefips > 99
replace str_pl_fips = "000" + string(placefips) if placefips < 100
replace str_pl_fips = "00000" if placefips == 0
gen str_st_fips = string(statefips) if statefips >= 10
replace str_st_fips ="0" +  string(statefips) if statefips < 10
gen uniq = str_st_fips + str_cty_fips + str_pl_fips

* Next, for each town, fill in missing information on government type based on past and future uses (for years in which the survey didn't mandate a response to gov't type question)
bysort uniq (year): ///
	replace gov_type = gov_type[_n-1] if gov_type == "." & gov_type[_n-1] != "."  & year == 2006 & W_ != 2002

bysort uniq (year): ///
	replace gov_type = gov_type[_n-1] if gov_type == "." & gov_type[_n-1] != "."  & year == 2011

replace gov_type = "At-large" if uniq == "5306165170" & year == 2006

save "icma_all_cleaned.dta", replace

* below code harmonizes placefips across years by identifying places with same town name, county, and state
* this helps match some additional towns across years

destring(uniq), replace
xtset uniq year

* create id variable based on combinations of string town names and county/state fips
egen u2 = group(place_icma countyfips statefips  place_type_icma)

* within an identifier, take placefips from 2001 observation if there is a mismatch (2001 has most complete place_fips data)
* if there is no 2001 observation, take most recent placefips

gen static_place = placefips if year == 2001
bys u2: carryforward static_place, replace
bys uniq: mipolate static_place year, gen(static2) backward
replace static_place = static2 if static_place == .
drop static2 
replace static_place = placefips if year == 2011 & static_place == .
bys uniq: mipolate static_place year, gen(static2) backward
replace static_place = static2 if static_place == .
drop static2 
replace static_place = placefips if year == 2006 & static_place == .
bys uniq: mipolate static_place year, gen(static2) backward
replace static_place = placefips if year == 2006 & static_place == .
drop static2 
replace static_place = placefips if year == 2006 & static_place == .
bys uniq: mipolate static_place year, gen(static2) backward
replace static_place = static2 if  static_place == .
drop static2 
replace static_place = placefips if year == 1996 & static_place == .
bys uniq: mipolate static_place year, gen(static2) backward
replace static_place = static2 if  static_place == .
drop static2 
replace static_place = placefips if year == 1991 & static_place == .
bys uniq: mipolate static_place year, gen(static2) backward
replace static_place = static2 if  static_place == .
drop static2 
replace static_place = placefips if year == 1986 & static_place == .
bys uniq: mipolate static_place year, gen(static2) backward
replace static_place = placefips if year == 1986 & static_place == .
replace static_place = static2 if  static_place == .
drop static2 
replace static_place = placefips if year == 1981 & static_place == .

* set this as your final placefips (note that you need to use statefips and countyfips as well to identify unique places)
drop placefips_final
rename static_place placefips

* save final file of ICMA data
save "icma_all_cleaned_v2.dta", replace